from flask import Flask,render_template
from flask import request,session,url_for,flash
from flask import current_app,g
from flask import make_response,redirect,abort
from flask_bootstrap import Bootstrap
from flask_moment import Moment
from flask_wtf import FlaskForm
from wtforms import StringField,SubmitField
from wtforms.validators import DataRequired
from flask_sqlalchemy import SQLAlchemy,Pagination
import mysql.connector
from flask_mail import Mail,Message
from werkzeug.security import generate_password_hash,check_password_hash
from collections import defaultdict
from datetime import datetime
from faker import Faker
from random import randint
import random
import math
from flask_pagedown import PageDown
from flask_pagedown.fields import PageDownField
#from flask_paginate import Pagination,get_page_parameter

pagedown = PageDown()
app = Flask(__name__,static_folder='static',static_url_path='/static',template_folder='templates')
bootstrap  = Bootstrap(app)
pagedown.init_app(app)

#session的存储容量有限
app.config['SECRET_KEY'] = 'don\'t want to guess my string'
app.config['MAIL_SERVER'] = 'smtp.qq.com'
app.config['MAIL_PORT'] ='465'
app.config['MAIL_USE_SSL'] = True
app.config['MAIL_USE_TLS'] =False
app.config['MAIL_USERNAME'] ='407282152@qq.com'
app.config['MAIL_PASSWORD'] ='lrhuzlwiosmibidc'
app.config['MAIL_DEFAULT_SENDER'] = '407282152@qq.com'


class PostForm(FlaskForm):
    title = PageDownField("输入标题",validators=[DataRequired()])
    body = PageDownField("输入博客内容",validators=[DataRequired()])

    submit = SubmitField('提交')

#app_ctx = app.app_context() #实例化一个应用上下文对象
@app.context_processor #定义一个全局变量，这样就可以不用给每个模板都传值了
def getnav():
    con = init_db()
    cursor = con.cursor(buffered=True)
    cursor.execute("select * from articalgeneral")
    result = cursor.fetchall()
    len1 = len(result)
    nav={}

    for i in range(0,len1):
        nav_key = result[i][0]
        nav_value = result[i][1]
        if nav_value:
            nav.setdefault(nav_key,[]).append(nav_value)
        else:
            nav[nav_key]=None
    cursor.close()
    con.close()
    return dict(nav=nav)

@app.context_processor
def queryrebook():
    con = init_db()
    cursor = con.cursor()

    cursor.execute('select * from rebook')
    rebook = cursor.fetchall()[0:3]
    cursor.close()
    con.close()

    return dict(rebook = rebook)

@app.context_processor
def queryregame():
    con = init_db()
    cursor = con.cursor()

    cursor.execute('select * from regame')
    regame = cursor.fetchall()[0:2]
    cursor.close()
    con.close()

    return dict(regame = regame)

def init_db():
    return mysql.connector.connect(user='nine',password='xw19960205',database='firstcon')

def init_admin():#初始化生成管理员权限
    con = init_db()
    cursor =con.cursor()
    password = generate_password_hash('xw19960205',method='pbkdf2:sha256',salt_length=8)
    username = '18868130372'
    cursor.execute(f'select * from admin where username like "18868130372"')
    result=cursor.fetchall()
    cursor.close()
    if result:
        con.close()
        return None
    else:
        cursor2=con.cursor()
        cursor2.execute(f'insert into admin(username,password,id) values("{username}","{password}","1")')
        con.commit()
        cursor.close()
        con.close()
        return None
init_admin()

def artical_pagination(page=1):
    con = init_db()
    cursor1 = con.cursor()
    cursor1.execute('select postdate,content,title,type,label from articalpost where type not in ("闲时所好","自作小说","游戏攻略")')
    resultlist = cursor1.fetchall()
    cursor1.close()
    con.close()
#定义paginate的各个属性
    total = len(resultlist)
    per_page = 6

#处理不同page的开始和结束位置，便于切片
    start = (page-1)*per_page
    end = start +per_page
    item = resultlist[start:end]
#处理每一页分页对象
    pagination = Pagination(resultlist,page,per_page,total,item)

    return pagination

def general_pagination(list,page=1):
    total = len(list)
    per_page = 6

    start = (page-1)*per_page
    end = start +per_page
    item = list[start:end]
#处理每一页分页对象
    pagination = Pagination(list,page,per_page,total,item)

    return pagination

@app.route('/') #route和下文使用的url_map一样，都是上文实例化出来的这个app（FLASK 对象）的属性
def index():
    '''一些app本身带的属性的调用展示
    user_agent = request.headers.get('User-Agent')#headers是一个字典，所以通过get方法，获得key为‘user-agent’的value
    list = request.headers.keys()
    app_ctx.push()#推送
    app_name =current_app.name#调用对象current_app
    app_ctx.pop()#移除
    map = app.url_map#调用app本身的url_map属性
    '''
    page = request.args.get('page')

    if page:
        page = int(page)
    else:
        page = 1

    pagination = artical_pagination(page)



    return render_template('index.html',pagination=pagination)

@app.route('/adminlogin',methods = ['GET','POST'])
def adminlogin():
    #form = NameForm()
    #if form.validate_on_submit():

    #if old_name is not None and old_name !=form.name.data:
    #flash("looks like you\'ve changed your name!")
    #将提交的表单数据，存入会话中,让用户免于多次填写
    if request.method=="GET":
        if session.get('username'):
            return redirect('/admin')
        else:
            return render_template('adminlogin.html',username = session.get('username'))
    else:
        con = init_db()
        cursor = con.cursor()
        username = request.form.get("username")
        password= request.form.get("password")
        cursor.execute(f'select * from admin where username like "{username}"')
        result =cursor.fetchone()
        cursor.close()
        con.close()
        if result:
            if check_password_hash(result[1],password):
                session['username'] = username
                return redirect('/admin')
            else:
                flash('密码错误，请重新输入')
                return render_template('adminlogin.html')
        else:
            flash('你的用户名不存在，请检查后重新输入')
            return render_template('adminlogin.html')
    #return redirect(url_for('test'))#重定向让用户刷新当前页面时，不会受到再次提交确认的弹窗信息

@app.route('/admin',methods=['GET','POST'])
def admin():
    if request.method=='GET':
        return render_template('admin.html',username = session.get('username'))
    else:
        return None

@app.route('/uppostkind',methods=['POST'])
def uppostkind():
    type = request.form.get('type')
    label = request.form.get('label')

    return redirect(url_for('uppost',type =type,label=label))

@app.route('/uppost',methods=['GET','POST'])
def uppost():
    session['type'] = request.args.get('type')
    session['label'] = request.args.get('label')
    form = PostForm()
    if request.method=='GET':
        return render_template('uppost.html',type=session['type'],label =session['label'],form=form)
    else:
        if form.validate_on_submit():
            type =session['type']
            label =session['label']
            content = form.body.data
            title =form.title.data
            date = datetime.now()

            con = init_db()
            cursor=con.cursor()

            cursor.execute(f'insert into articalpost(postdate,content,title,type,label) values("{date}","{content}","{title}","{type}","{label}")')
            con.commit()
            cursor.close()
            con.close()

            flash('创建文章成功。')
            return redirect(url_for('uppost',type = session['type'],label=session['label']))

@app.route('/postmanage',methods=['GET','POST'])
def postmanage():
    if request.method =='GET':

        con = init_db()
        cursor = con.cursor()
        cursor.execute('select * from articalpost order by postdate desc')
        result = cursor.fetchall()

        return render_template('postmanage.html',result =result)
    else:
        return None

@app.route('/postdetail')
def postdetail():
    id = int(request.args.get('id'))
    con = init_db()
    cursor = con.cursor()
    cursor.execute(f'select * from articalpost where id  = {id}')
    result = cursor.fetchall()[0]
    return render_template('artical.html',id = id,result=result)

@app.route('/editpost',methods=['GET','POST'])
def editpost():
    id = int(request.args.get('id'))
    con = init_db()
    cursor = con.cursor()
    cursor.execute(f'select * from articalpost where id ={id}')
    result = cursor.fetchall()[0]
    cursor.close()
    form = PostForm()


    if request.method =='GET':
        form.body.data =  result[2]
        form.title.data = result[3]
        return render_template('editpost.html',result= result,form = form)
    else:
        if form.validate_on_submit():
            type = result[4]
            label =result[5]
            content = form.body.data
            title = form.title.data
            date = datetime.now()

            cursor = con.cursor()
            cursor.execute(f'UPDATE articalpost a SET a.postdate="{date}",a.content="{content}",a.title="{title}" where id = {id}')
            con.commit()
            cursor.close()
            con.close()
            flash('修改博文成功')
            return redirect(url_for('postmanage'))

@app.route('/delpost')
def delpost():
    id = int(request.args.get('id'))
    con = init_db()
    cursor = con.cursor()
    cursor.execute(f'delete from articalpost where id ={id}')
    con.commit()
    cursor.close()
    con.close()
    flash('博文删除成功')
    return redirect(url_for('postmanage'))

@app.route('/typelist',methods=['GET'])
def typelist():
    type = request.args.get('type')
    page = request.args.get('page')

    if page:
        page = int(page)
    else:
        page = 1

    con = init_db()
    cursor = con.cursor()
    cursor.execute(f'select * from articalpost where type like "{type}" order by postdate desc')
    result = cursor.fetchall()
    cursor.close()
    con.close()

    pagination = general_pagination(result,page)

    if type == '自作小说':
        return redirect(url_for('novels'))
    else:
        return render_template('typelist.html',pagination =pagination,type = type)

@app.route('/novels')
def novels():
    con = init_db()
    cursor = con.cursor()
    cursor.execute('select label,status from  novel_intro')
    status = cursor.fetchall()[0][1]

    #查数据库，找出最新发布的作品,再回查计算字数
    cursor.execute('select * from novelchapter order by date desc')
    chapter = cursor.fetchall()[0]
    novel =chapter[2]
    date =chapter[5]
    cursor.close()
    cursor1 = con.cursor()
    cursor1.execute(f'select num from novelchapter where novel like "{novel}"')
    num = cursor1.fetchall()
    numsum = 0
    for item in num:
        numsum += item[0]
    lastpoinfo = [novel,status,numsum,date]

    #查数据库，返回所有小说的信息
    cursor1.execute('select label,status from novel_intro')
    novelgeneral = cursor1.fetchall()
    titlelist = []
    novelinfo = {}
    for item in novelgeneral:
        novelinfo.setdefault(item[0],[]).append(item[1])

    #利用for循环，遍历小说名，进行数据库查询，将返回的结果也放入列表即可。
    for key in novelinfo:
        cursor1.execute(f'select novel,num from novelchapter  where novel like "{key}"')
        result = cursor1.fetchall()
        numsum = 0
        for item in result:
            numsum += item[1]
        novelinfo.setdefault(key,[]).append(numsum)

        cursor1.execute(f'select date from novelchapter where novel like "{key}" order by date desc limit 1')
        date = cursor1.fetchall()[0][0]
        novelinfo.setdefault(key,[]).append(date)
    #return f'{novelinfo}'
    return render_template('novelslist.html',lastpoinfo = lastpoinfo,novelinfo = novelinfo)

@app.route('/labellist',methods=['GET'])
def labellist():
    label = request.args.get('label')
    page = request.args.get('page')

    if page:
        page = int(page)
    else:
        page = 1

    con = init_db()
    cursor = con.cursor()
    cursor.execute(f'select * from articalpost where label like "{label}" order by postdate desc')
    result = cursor.fetchall()
    cursor.close()


    for item in result:
        if item[4] != '自作小说':
            continue
        else:
            return redirect(url_for('novels_intro',label = item[5]))

    pagination = general_pagination(result,page)
    con.close()
    return render_template('labellist.html',pagination =pagination,label = label)

@app.route('/novels_intro')
def novels_intro():
    label = request.args.get('label')
    con = init_db()
    cursor =con.cursor()
    cursor.execute(f'select * from novel_intro where label like "{label}"')
    novelinfo = cursor.fetchone()

    cursor.execute(f'select * from novelchapter where novel like "{label}" order by chapter desc')
    result = cursor.fetchall()

    #将result转换为增序排列，如果没有结果，则置为空
    if  result:
        chapterlist = reverselist(result)
    else:
        chapterlist = None

    #获取按卷标为key的章节字典
    cursor.execute(f'select chapter,title,general,num,date,juan from novelchapter where novel like "{label}" order by  chapter ')
    result= cursor.fetchall()
    re_len = len(result)
    noveldict = {}

    for i in range(0,re_len):
        novel_key = result[i][5]
        novel_value = list(result[i][0:5])
        noveldict.setdefault(novel_key,[]).append(novel_value)

    return render_template('novels_intro.html',label  = label,novelinfo = novelinfo,noveldict = noveldict,novel =label)

@app.route('/chapter')
def chapter():
    novel = request.args.get('novel')
    chapter = request.args.get('chapter')
    chapter = int(chapter)
    con = init_db()
    cursor = con.cursor()
    cursor.execute(f'select * from novelchapter where novel like "{novel}" and chapter = {chapter}')
    result = cursor.fetchone()
    cursor.close()
    cursor1 = con.cursor()
    cursor1.execute(f'select chapter,title from novelchapter where novel like "{novel}"')
    allchapter = cursor1.fetchall()

    con.close()

    return render_template('chapter.html',novel =novel,result=result,allchapter=allchapter)

@app.route('/mail')#这个可以用来以后如果有文章更新，订阅了的人，我更新就给他推送邮件。
def mail():
    msg = Message(subject='Hello world!',sender="407282152@qq.com",recipients=["18868130372@163.com"])
    msg.body = "testing by flask-email"
    msg.html="<b>testing</b>"
    mail = Mail(app)
    mail.send(msg)
    return '<h1>发送成功</h1>'

@app.route('/upnovel',methods=['GET','POST'])
def upnovel():
    if request.method == 'GET':
        return render_template('upnovel.html',type = '自作小说')
    else:
        label = request.form.get('label')
        type = request.form.get('type')
        intro =request.form.get('intro')
        oneintro =request.form.get('oneintro')
        actor = request.form.get('actor')
        support =request.form.get('support')
        status = '连载中'

        con = init_db()
        cursor = con.cursor()
        cursor.execute(f'insert into novel_intro(label,type,intro,oneintro,actor,support,status) values("{label}","{type}","{intro}","{oneintro}","{actor}","{support}","{status}")')
        con.commit()
        cursor.close()


        con.close()

        return render_template('upnovelchapter.html')

@app.route('/choosenovel',methods=['GET'])
def choosenovel():
    if request.method =='GET':
        con = init_db()
        cursor = con.cursor()
        cursor.execute('select label from novel_intro')
        result = cursor.fetchall()
        con.commit()
        cursor.close()
        return render_template('choosenovel.html',novel = result)

@app.route('/upnovelchapter',methods=['GET','POST'])
def upnovelchapter():
    novel = request.args.get('novel')
    if request.method == 'GET':
        con = init_db()
        cursor = con.cursor()
        cursor.execute(f'select * from novelchapter where novel like "{novel}" order by chapter desc')
        result = cursor.fetchall()
        con.commit()
        cursor.close()

        if  result:
            newchapter = result[0][0]+1
            chapterlist = reverselist(result)
        else:
            chapterlist = None
            newchapter = 1

        session['newchapter'] = newchapter
        session['novel'] = novel

        #获取按卷标为key的章节字典
        cursor = con.cursor()
        cursor.execute(f'select chapter,title,general,num,date,juan from novelchapter where novel like "{novel}" order by  chapter ')
        result= cursor.fetchall()
        re_len = len(result)
        noveldict = {}

        for i in range(0,re_len):
            novel_key = result[i][5]
            novel_value = list(result[i][0:5])
            noveldict.setdefault(novel_key,[]).append(novel_value)

        session['noveldict'] = noveldict

        return render_template('upnovelchapter.html',noveldict=session.get('noveldict'),chapterlist = session.get('chapterlist'),newchapter = session.get('newchapter'),novel = session.get('novel'))
    else:
        novel = request.args.get('novel')
        chapter = int(request.args.get('newchapter'))

        title = request.form.get('title')
        general = request.form.get('general')
        content = request.form.get('content')
        authorsay = request.form.get('authorsay')

        date = datetime.now()
        num = len(content)

        con = init_db()
        cursor = con.cursor()
        cursor.execute(f'select * from novelchapter where novel like "{novel}" and chapter={chapter-1}')
        juan  =  cursor.fetchone()
        if juan:
            juan = juan[9]
        else:
            juan = ""
        cursor.execute(f'insert into novelchapter(chapter,title,novel,general,num,authorsay,content,date,juan) values("{chapter}","{title}","{novel}","{general}","{num}","{authorsay}","{content}","{date}","{juan}")')
        con.commit()
        cursor.close()

        cursor1 = con.cursor()
        cursor1.execute(f'select * from novelchapter where novel like "{novel}" order by chapter desc')
        result = cursor1.fetchall()
        if  result:
            session['newchapter'] = result[0][0]+1
        else:
            session['newchapter'] = 1
        cursor1.close()
        con.close()

        flash('上传成功')

        session['novel'] = novel

        return redirect(url_for('upnovelchapter',novel = session.get('novel')))

@app.route('/printdictnovellist',methods=['GET'])
def printdictnovellist():
    novel = request.args.get('novel')
    con = init_db()
    cursor = con.cursor()
    cursor.execute(f'select chapter,title,general,num,date,juan from novelchapter where novel like "{novel}" order by  chapter ')
    result= cursor.fetchall()
    re_len = len(result)-1
    dict = {}


    for i in range(0,re_len):
        key = result[i][5]
        value = result[i][0:5]
        dict.setdefault(key,[]).append(value)


    return f'{dict}'

#利用session可以实现保存客户端get传递的数据，在后续的程序中继续调用变量。
#request.form.to_dict()，可以查出表单提交了哪些数据，包括提交的表单按钮，如果点击了x按钮，那么x按钮的name会作为key值出现在dict中。
@app.route('/chaptermanage',methods = ['POST'])
def chaptermanage():
    chapter = request.form.get('chapter')
    novel = request.args.get('novel')
    if chapter and novel:
        dict = request.form.to_dict()
        if 'addjuan' in dict:
            return render_template('addjuan.html',novel = novel,chapter=chapter)
        else:
            return redirect(url_for('editchapter',chapter = chapter,novel=novel))
    else:
        flash('请选择章节')
        return redirect(url_for('upnovelchapter',novel = novel))

@app.route('/editchapter',methods = ['GET','POST'])
def editchapter():
    novel = request.args.get('novel')
    chapter = request.args.get('chapter')
    chapter = int(chapter)
    con = init_db()
    cursor = con.cursor()
    if request.method =='GET':
        cursor.execute(f'select * from novelchapter where chapter = {chapter} and novel like "{novel}"')
        content = cursor.fetchone()
        cursor.close()
        con.close()
        return render_template('editchapter.html',novel = novel,chapter = chapter,content=content)
    else:
        title = request.form.get('title')
        general =request.form.get('general')
        content = request.form.get('content')
        authorsay =request.form.get('authorsay')
        num = len(content)
        date = datetime.now()

        if title and general and content:
            cursor.execute(f'UPDATE novelchapter n SET n.content = "{content}",n.general = "{general}",n.num ="{num}",n.title="{title}",n.authorsay = "{authorsay}",n.date = "{date}" where novel like "{novel}" and chapter = {chapter}')
            con.commit()
            cursor.close()
            con.close()
            flash('修改成功')
            return redirect(url_for('editchapter',novel=novel,chapter=chapter))
        else:
            cursor.close()
            con.close()
            flash('标题，内容提要，本章内容均不得为空')
            return redirect(url_for('editchapter',novel=novel,chapter=chapter))
@app.route('/addjuanbiao',methods = ['POST'])
def addjuanbiao():
    chapter = int(request.args.get('chapter'))
    novel = request.args.get('novel')
    juan = request.form.get('juanbiao')

    con =init_db()
    cursor=con.cursor()
    cursor.execute(f'select juan from novelchapter where novel like "{novel}" and chapter = {chapter}')
    oldjuan = cursor.fetchone()[0]
    cursor.execute(f'UPDATE novelchapter SET juan = "{juan}" where novel like "{novel}" and chapter >= {chapter} and juan like "{oldjuan}"')
    con.commit()
    cursor.close()
    con.close()

    return redirect(url_for('upnovelchapter',novel = novel))

@app.route('/updatejuan',methods=['GET'])
def updatejuan():
    novel = request.args.get('novel')
    juan = request.args.get('juan')

    return render_template('updatejuanbiao.html',novel = novel,juan = juan)

@app.route('/updatejuanbiao',methods=['POST'])
def updatejuanbiao():
    novel = request.args.get('novel')
    juan = request.args.get('juan')
    updatevalue = request.form.get('updatevalue')

    con = init_db()
    cursor = con.cursor()

    cursor.execute(f'UPDATE novelchapter SET juan = "{updatevalue}" where novel like "{novel}" and juan like "{juan}"')
    con.commit()
    cursor.close()
    con.close()
    return redirect(url_for('upnovelchapter',novel = novel))

@app.route('/deletejuan')
def deletejuan():
    novel = request.args.get('novel')
    juan = request.args.get('juan')
    leastchapter = int(request.args.get('leastchapter'))
    biggestchapter = int(request.args.get('biggestchapter'))

    con = init_db()
    cursor = con.cursor()
    #要就被删除的卷标的最小章节前是否还有章节来讨论
    if (leastchapter==1):
        cursor.execute(f'UPDATE novelchapter SET juan = "" where novel like "{novel}" and chapter between {leastchapter} and {biggestchapter}')
        con.commit()
        cursor.close()
        con.close()
        return redirect(url_for('upnovelchapter',novel = novel))
    else:
        cursor.execute(f'select juan from novelchapter where novel like "{novel}" and chapter = {leastchapter-1}')
        newjuan = cursor.fetchone()[0]
        cursor.execute(f'UPDATE novelchapter SET juan = "{newjuan}" where novel like "{novel}" and chapter between {leastchapter} and {biggestchapter}')
        con.commit()
        cursor.close()
        con.close()
        return redirect(url_for('upnovelchapter',novel = novel))

@app.route('/upfile',methods=['POST'])
def upfile():
    type = request.form.get("type")
    label = request.form.get('label')
    title =request.form.get('title')
    content = request.form.get('content')
    time =datetime.now()

    return f'<h3>type:{type}</h3>'+f'<h3>label:{label}</h3>'+f'<h3>title:{title}</h3>'+f'<p>posttime:{time}</p>'+f'<p>content:{content}</p>'

'''如果直接返回nav，显示的是十六进制数据，好大的坑！'''
@app.route('/upfake',methods=['GET'])
def upfake():
    if request.method =='GET':
        con = init_db()
        cursor1 = con.cursor()
        cursor1.execute('select * from articalgeneral')
        result = cursor1.fetchall()
        len1 = len(result)
        nav={}

        for i in range(0,len1):
            nav_key = result[i][0]
            nav_value = result[i][1]
            if nav_value:
                nav.setdefault(nav_key,[]).append(nav_value)
            else:
                nav[nav_key]=None
        cursor1.close()
        cursor2=con.cursor()
        fake = Faker()
        key =list(nav.keys())
        for i in range(0,10):
            date = fake.date()
            content = fake.text()
            title = fake.word()
            type = key[random.randint(0,len(key)-1)]
            value = nav.get(type)
            if value:
                label = value[random.randint(0,len(value)-1)]
            else:
                label = None
            cursor2.execute(f'insert into articalpost(postdate,content,title,type,label) values("{date}","{content}","{title}","{type}","{label}")')
            con.commit()
        cursor2.close()

        con.close()
        flash("生成虚拟文章成功")
        return render_template('admin.html',username = session.get('username'))

@app.route('/paginate_test/<int:page>',methods=['GET'])
def paginate_test(page=1):
#获取最近发布的所有文章结果，返回一个列表
    con = init_db()
    cursor1 = con.cursor()
    cursor1.execute('select postdate,content,title,type,label from articalpost where type not in ("闲时所好","自作小说","游戏攻略")')
    resultlist = cursor1.fetchall()
    cursor1.close()
    con.close()
#定义paginate的各个属性
    total = len(resultlist)

    per_page = 3


#处理不同page的开始和结束位置，便于切片
    start = (page-1)*per_page
    end = start +per_page
    item = resultlist[start:end]
#处理每一页分页对象
    pagination = Pagination(resultlist,page,per_page,total,item)

    return render_template('paginate_test.html',pagination=pagination)

@app.route('/querytest',methods = ['GET'])
def querytest():
    if request.method == 'GET':
        con = init_db()
        cursor1 = con.cursor()
        cursor1.execute('select postdate,content,title,type,label from articalpost where type not in ("闲时所好","自作小说","游戏攻略")')
        resultlist = cursor1.fetchall()
        cursor1.close()
        con.close()
    return f'{resultlist}'

def reverselist(list):
    a = list
    b =[]
    while len(a)>0:
        b.append(a[-1])
        a.pop()
    return b

@app.route('/upbook',methods=['GET'])
def upbook():
    if request.method =='GET':
        con = init_db()
        fake = Faker()
        for i in range(0,10):
            title = fake.word()
            author = fake.name()
            recommedation = fake.text()
            if len(recommedation)>20:
                recommedation  = recommedation[0:20]
            rate = random.randint(0,10)
            downurl = fake.uri()
            imgurl = url_for('static',filename = 'img/huayellow.jpg')

            cursor = con.cursor()
            cursor.execute(f'insert into rebook(title,author,recommedation,rate,downurl,imgurl) values("{title}","{author}","{recommedation}","{rate}","{downurl}","{imgurl}")')
            con.commit()

        cursor.close()
        con.close()
        flash("生成推荐书本成功")
        return render_template('admin.html',username = session.get('username'))

@app.route('/upgame',methods=['GET'])
def upgame():
    if request.method =='GET':
        con = init_db()
        fake = Faker()
        for i in range(0,10):
            title = fake.word()
            company = fake.company()
            rate = random.randint(0,10)
            downurl = fake.uri()
            imgurl = url_for('static',filename='img/tiance.jpg')

            cursor = con.cursor()
            cursor.execute(f'insert into regame(title,company,rate,downurl,imgurl) values("{title}","{title}","{rate}","{downurl}","{imgurl}")')
            con.commit()

        cursor.close()
        con.close()
        flash("生成推荐游戏成功")
        return render_template('admin.html',username = session.get('username'))

@app.errorhandler(404)
def page_not_found(error):
    return render_template('404.html'),404

'''返回一个400请求，请求无效
@app.route('/status')
def status():
    return 'bad request.',400
'''
'''重定向
@app.route('/redirect')
def redirecttest():
    return redirect('http://www.baidu.com')
'''
'''abort
@app.route('/users/int:<id>')
def get_user(id):
    if not id:
        abort(404)
    return 'YOur id is {}'.format(id)
'''
'''取nav的实验
@app.route('/nav')
def nav():
    con = init_db()
    cursor = con.cursor(buffered=True)
    cursor.execute("select * from articalgeneral")
    result = cursor.fetchall()
    len1 = len(result)
    nav={}

    for i in range(0,len1):
        nav_key = result[i][0]
        nav_value = result[i][1]
        nav.setdefault(nav_key,[]).append(nav_value)

    cursor.close()
    con.close()
    return f"{nav}"
'''


if __name__ == '__main__':
    app.run(debug=True)
